"""
Case Type   : Length
Case Name   : 数据库兼容类型为PG，行存、行存向量化、列存（开codegen）三种存储下length函数可正常使用
Create At   : 2022.11.19
Owner       : @li-xin12345
Description :
    1、创建PG库，使用PG库
    2、行存存储模式下使用length函数
    3、行存向量化存储模式下使用length函数
    4、列存（开codegen）存储模式下使用length函数
    5、清理环境
Expect      :
    1、成功
    2、成功
    3、查询结果与步骤2一致
    4、查询结果与步骤2一致
    5、清理成功
History     :
    Modifide by li-xin12345 at 2024.9.3：优化环境清理
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class FuncLength01(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.user_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.vector_default = self.common.show_param(
            'try_vector_engine_strategy')
        self.codegen_default = self.common.show_param(
            'enable_codegen')
        self.dbname = 'db_pg_lebgth_0001'
        self.table_name01 = 't_pg_length_0001_01'
        self.table_name02 = 't_pg_length_0001_02'
        status = self.sh_primary.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)

    def test_length(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '-----step1:创建PG库，使用PG库;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.dbname};" \
                  f"create database {self.dbname} dbcompatibility ='PG';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)

        text = '-----step2:行存存储模式下使用length函数;expect:成功-----'
        self.log.info(text)
        text = '-----step2.1:设置try_vector_engine_strategy=off;expect:' \
               '成功-----'
        self.log.info(text)
        if "off" not in self.vector_default:
            res = self.sh_primary.execute_gsguc(
                "set", self.constant.GSGUC_SUCCESS_MSG,
                f"try_vector_engine_strategy=off")
            self.log.info(res)
            status = self.sh_primary.restart_db_cluster()
            self.log.info(status)
            res = self.sh_primary.execut_db_sql(
                "show try_vector_engine_strategy;",
                dbname=self.user_node.db_name)
            self.log.info(res)
            self.assertEqual("off", res.splitlines()[-2].strip(),
                             "执行失败" + text)

        text = '-----step2.2:建表并插入数据;expect:成功-----'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.table_name01};
        create table {self.table_name01}(a char(10), b varchar(10)) 
        with (orientation=row);
        insert into {self.table_name01} values('零0','零1二3');
        insert into {self.table_name01} values('','');
        insert into {self.table_name01} values('0','0');
        '''
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      '执行失败:' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败:' + text)
        self.assertEqual(sql_res.count(self.constant.INSERT_SUCCESS_MSG), 3,
                         "执行失败:" + text)

        text = '-----step2.3:使用length函数;expect:成功-----'
        self.log.info(text)
        sql_cmd = f'select length(a), a from {self.table_name01} order by 1;'
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        self.assertEqual('3 rows', sql_res.splitlines()[-1].replace("(", "").
                         replace(")", ""), '执行失败:' + text)

        baseline = sql_res
        self.log.info('baseline: ' + baseline)

        text = '-----step3:行存向量化存储模式下使用length函数;' \
               'expect:查询结果与步骤2一致-----'
        self.log.info(text)
        text = '-----step3.1:设置set try_vector_engine_strategy=force，' \
               '使用length;expect:成功-----'
        self.log.info(text)
        res = self.sh_primary.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"try_vector_engine_strategy=force")
        self.log.info(res)
        status = self.sh_primary.restart_db_cluster()
        self.log.info(status)
        res = self.sh_primary.execut_db_sql(
            "show try_vector_engine_strategy;",
            dbname=self.user_node.db_name)
        self.log.info(res)
        self.assertEqual("force", res.splitlines()[-2].strip(),
                         "执行失败" + text)

        sql_cmd = f'select length(a), a from {self.table_name01} order by 1;'
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res, baseline, "查询结果与步骤2不一致" + text)

        text = '-----step3.2:设置set try_vector_engine_strategy=optimal，' \
               '使用length;expect:成功-----'
        self.log.info(text)
        self.log.info(text)
        res = self.sh_primary.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"try_vector_engine_strategy=optimal")
        self.log.info(res)
        status = self.sh_primary.restart_db_cluster()
        self.log.info(status)
        res = self.sh_primary.execut_db_sql(
            "show try_vector_engine_strategy;",
            dbname=self.user_node.db_name)
        self.log.info(res)
        self.assertEqual("optimal", res.splitlines()[-2].strip(),
                         "执行失败" + text)

        sql_cmd = f'select length(a), a from {self.table_name01} order by 1;'
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res, baseline, "查询结果与步骤2不一致" + text)

        text = '-----step4:行存向量化存储模式下使用length函数;' \
               'expect:查询结果与步骤2一致-----'
        self.log.info(text)
        text = '-----step4.1:设置允许开启代码生成优化;expect:成功-----'
        self.log.info(text)
        if "on" not in self.codegen_default:
            res = self.sh_primary.execute_gsguc(
                "set", self.constant.GSGUC_SUCCESS_MSG, "enable_codegen=on")
            self.log.info(res)
            status = self.sh_primary.restart_db_cluster()
            self.log.info(status)
            res = self.sh_primary.execut_db_sql("show enable_codegen;",
                                                dbname=self.user_node.db_name)
            self.log.info(res)
            self.assertEqual("on", res.splitlines()[-2].strip(),
                             "执行失败" + text)

        text = '-----step4.2:建表并插入数据;expect:成功-----'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.table_name02};
                create table {self.table_name02}(a char(10), b varchar(10)) 
                with (orientation=column);
                insert into {self.table_name02} values('零0','零1二3');
                insert into {self.table_name02} values('','');
                insert into {self.table_name02} values('0','0');
                '''
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      '执行失败:' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败:' + text)
        self.assertEqual(sql_res.count(self.constant.INSERT_SUCCESS_MSG), 3,
                         "执行失败:" + text)

        text = '-----step4.3:使用length函数;expect:成功-----'
        self.log.info(text)
        sql_cmd = f'select length(a), a from {self.table_name02} order by 1;'
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=self.user_node.db_name)
        self.log.info(sql_res)
        self.assertEqual('3 rows', sql_res.splitlines()[-1].replace("(", "").
                         replace(")", ""), '执行失败:' + text)
        self.assertEqual(sql_res, baseline, "查询结果与步骤2不一致" + text)

    def tearDown(self):
        text = '-----step6:清理环境;expect:成功-----'
        self.log.info(text)
        sql_cmd = f'drop database if exists {self.dbname};'
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_res)
        restore_vector_default = self.sh_primary.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"try_vector_engine_strategy={self.vector_default}")
        self.log.info(restore_vector_default)
        restore_codegen_default = self.sh_primary.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"enable_codegen={self.codegen_default}")
        self.log.info(restore_codegen_default)
        self.sh_primary.restart_db_cluster()
        status = self.sh_primary.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)
        self.assertTrue("Normal" in status or "Degraded" in status)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
